﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Code.DAL
{
    public class DBCon
    {
        public string StrCon = string.Empty;
        private string getCon(string serverName, string userName, string pwd)
        {
            StrCon = string.Format(@"Data Source={0};Initial Catalog=master;User ID={1};Password={2}", serverName, userName, pwd);
            return StrCon;
        }

        private string getCon(string serverName, string userName, string pwd, string db)
        {
            StrCon = string.Format(@"Data Source={0};Initial Catalog={1};User ID={2};Password={3}", serverName, db, userName, pwd);
            return StrCon;
        }

        public List<string> getDataBase(string serverName, string userName, string pwd)
        {
            string strCon = getCon(serverName, userName, pwd);
            string sql = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, strCon);
            DataTable tb = new DataTable();
            adapter.Fill(tb);
            List<string> lst = new List<string>();
            string[] s = new[] { "master", "model", "msdb", "tempdb" };
            foreach (DataRow row in tb.Rows)
            {
                string str = row[0].ToString();
                if (!s.Contains(str))
                {
                    lst.Add(str);
                }
            }
            return lst;
        }

        public DataBase DB;
        public void CreateTable(string serverName, string userName, string pwd, string strDB)
        {
            string strCon = getCon(serverName, userName, pwd, strDB);
            //得到SQLServer 版本
            DataBase db = new DataBase();
            String sqlVersion = "select @@VERSION";
            DataTable temp = GetDataTable(strCon, sqlVersion);
            DataRow row = temp.Rows[0];
            if (row[0].ToString().StartsWith("Microsoft SQL Server 2005"))
            {
                db.VER = VERSION.SQLServer_2005;
            }
            else if (row[0].ToString().StartsWith("Microsoft SQL Server  2000"))
            {
                db.VER = VERSION.SQLServer_2000;
            }
            DataTable tbJG = null;
            if (db.VER == VERSION.SQLServer_2000)
            {
                tbJG = GetDataTable(strCon, Code.Properties.Resources.Sql2000);
            }
            else if (db.VER == VERSION.SQLServer_2005)
            {
                tbJG = GetDataTable(strCon, Code.Properties.Resources.Sql2005);
            }
            CreateColumn(tbJG, db);
            DB = db;
        }

        public DataTable GetDataTable(string strCon, string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, strCon);
            DataTable tb = new DataTable();
            adapter.Fill(tb);
            return tb;
        }

        public DataSet GetDataSet(string strCon, string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, StrCon);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        private void CreateColumn(DataTable tbJG, DataBase db)
        {
            Table tb = null;
            DataTable foreignTable = null;
            foreach (DataRow row in tbJG.Rows)
            {
                if (!string.IsNullOrEmpty(row["表名"].ToString()))
                {
                    tb = new Table();
                    tb.Name = row["表名"].ToString();
                    db.Tbs.Add(tb);
                    DataSet ds = GetDataSet(StrCon, "exec sp_helpconstraint   '" + row["表名"].ToString() + "' ");
                    foreach(DataTable temp in ds.Tables)
                    {
                        if(temp.Columns.Count==7)
                        {
                            foreignTable=temp;
                            break;
                        }
                    }
                }
                Column col = new Column();
                col.Type = row["类型"].ToString();
                col.Name = row["字段名"].ToString();
                col.Length = int.Parse(row["占用字节数"].ToString());
                if (row["主键"].ToString() == "√")
                {
                    col.IsKey = true;
                }
                else
                {
                    col.IsKey = false;
                }
                if (row["标识"].ToString() == "√")
                {
                    col.IsIdentity = true;
                }
                else
                {
                    col.IsIdentity = false;
                }
                col.Desc = row["字段说明"].ToString();
                tb.Columns.Add(col);
                
                //判断这个字段是否存在外键
                if (foreignTable != null)
                {
                    for (int i = 0; i < foreignTable.Rows.Count; i++)
                    {
                        DataRow foreignRow = foreignTable.Rows[i];
                        if (foreignRow["constraint_type"].ToString() == "FOREIGN KEY")
                        {
                            if (foreignRow["constraint_keys"].ToString() == col.Name)
                            {
                                foreignRow = foreignTable.Rows[i + 1];
                                string strTemp = foreignRow["constraint_keys"].ToString();
                                strTemp = strTemp.Substring(20, strTemp.LastIndexOf(" ") - strTemp.LastIndexOf("."));
                                i++;
                            }
                        }
                    }
                }
            }

        }

    }
}
